{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-12-08T08:56:42.301448Z",
     "start_time": "2019-12-08T08:56:42.247995Z"
    }
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import MetaData\n",
    "metadata = MetaData()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-12-08T08:56:42.613040Z",
     "start_time": "2019-12-08T08:56:42.589960Z"
    }
   },
   "outputs": [],
   "source": [
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-12-08T09:22:52.949327Z",
     "start_time": "2019-12-08T09:22:52.921170Z"
    }
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import (Table, Column, Integer, Numeric, String, ForeignKey, \n",
    "                        DateTime, CheckConstraint, Index, PrimaryKeyConstraint, ForeignKeyConstraint)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-12-08T08:56:43.296207Z",
     "start_time": "2019-12-08T08:56:43.278401Z"
    }
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-12-08T08:56:43.744642Z",
     "start_time": "2019-12-08T08:56:43.719050Z"
    }
   },
   "outputs": [],
   "source": [
    "engine = create_engine('sqlite:///:memory:')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-12-08T08:56:44.363872Z",
     "start_time": "2019-12-08T08:56:44.334566Z"
    }
   },
   "outputs": [],
   "source": [
    "cookies = Table('cookies', metadata,\n",
    "    Column('cookie_id', Integer(), primary_key=True),\n",
    "    Column('cookie_name', String(50), index=True),\n",
    "    Column('cookie_recipe_url', String(255)),\n",
    "    Column('cookie_sku', String(55)),\n",
    "    Column('quantity', Integer()),\n",
    "    Column('unit_cost', Numeric(12, 2)),\n",
    "    CheckConstraint('unit_cost >= 0.00', name='unit_cost_positive')     \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-12-08T08:56:45.012440Z",
     "start_time": "2019-12-08T08:56:44.986346Z"
    }
   },
   "outputs": [],
   "source": [
    "users = Table('users', metadata,\n",
    "    Column('user_id', Integer(), primary_key=True),\n",
    "    Column('username', String(15), nullable=False, unique=True),\n",
    "    Column('email_address', String(255), nullable=False),\n",
    "    Column('phone', String(20), nullable=False),\n",
    "    Column('password', String(25), nullable=False),\n",
    "    Column('created_on', DateTime(), default=datetime.now),\n",
    "    Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-12-08T08:56:45.386578Z",
     "start_time": "2019-12-08T08:56:45.353170Z"
    }
   },
   "outputs": [],
   "source": [
    "orders = Table('orders', metadata,\n",
    "    Column('order_id', Integer(), primary_key=True),\n",
    "    Column('user_id', ForeignKey('users.user_id')),\n",
    ")\n",
    "\n",
    "line_items = Table('line_items', metadata,\n",
    "    Column('line_items_id', Integer(), primary_key=True),\n",
    "    Column('order_id', ForeignKey('orders.order_id')),\n",
    "    Column('cookie_id', ForeignKey('cookies.cookie_id')),\n",
    "    Column('quantity', Integer()),\n",
    "    Column('extended_cost', Numeric(12, 2))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-12-08T08:58:13.540348Z",
     "start_time": "2019-12-08T08:58:13.479280Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['append_ddl_listener',\n",
       " 'bind',\n",
       " 'clear',\n",
       " 'create_all',\n",
       " 'dispatch',\n",
       " 'drop_all',\n",
       " 'get_children',\n",
       " 'info',\n",
       " 'is_bound',\n",
       " 'naming_convention',\n",
       " 'quote',\n",
       " 'reflect',\n",
       " 'remove',\n",
       " 'schema',\n",
       " 'sorted_tables',\n",
       " 'tables']"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[v for v in dir(metadata) if not v.startswith('_')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-12-08T08:48:27.620716Z",
     "start_time": "2019-12-08T08:48:27.562716Z"
    }
   },
   "outputs": [],
   "source": [
    "metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-12-08T09:02:13.453170Z",
     "start_time": "2019-12-08T09:02:13.392931Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PrimaryKeyConstraint(Column('cookie_id', Integer(), table=<cookies>, primary_key=True, nullable=False))\n",
      "\n",
      "CheckConstraint(<sqlalchemy.sql.elements.TextClause object at 0x10e740470>, name='unit_cost_positive', table=Table('cookies', MetaData(bind=None), Column('cookie_id', Integer(), table=<cookies>, primary_key=True, nullable=False), Column('cookie_name', String(length=50), table=<cookies>), Column('cookie_recipe_url', String(length=255), table=<cookies>), Column('cookie_sku', String(length=55), table=<cookies>), Column('quantity', Integer(), table=<cookies>), Column('unit_cost', Numeric(precision=12, scale=2), table=<cookies>), schema=None))\n",
      "\n"
     ]
    }
   ],
   "source": [
    "[v for v in dir(metadata.tables['cookies']) if not v.startswith('_')]\n",
    "for c in cookies.constraints:\n",
    "    print(c)\n",
    "    print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-12-08T09:07:55.799575Z",
     "start_time": "2019-12-08T09:07:55.767825Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index('ix_test', Column('cookie_sku', String(length=55), table=<cookies>), Column('cookie_name', String(length=50), table=<cookies>))"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Index(\"ix_test\", cookies.c.cookie_sku, cookies.c.cookie_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-12-08T09:09:37.174718Z",
     "start_time": "2019-12-08T09:09:37.107805Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PrimaryKeyConstraint(Column('cookie_id', Integer(), table=<cookies>, primary_key=True, nullable=False))"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cookies.primary_key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-12-08T09:09:45.978384Z",
     "start_time": "2019-12-08T09:09:45.954756Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{Index('ix_cookies_cookie_name', Column('cookie_name', String(length=50), table=<cookies>)),\n",
       " Index('ix_test', Column('cookie_sku', String(length=55), table=<cookies>), Column('cookie_name', String(length=50), table=<cookies>))}"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cookies.indexes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-12-08T09:09:57.615162Z",
     "start_time": "2019-12-08T09:09:57.586487Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{CheckConstraint(<sqlalchemy.sql.elements.TextClause object at 0x10e740470>, name='unit_cost_positive', table=Table('cookies', MetaData(bind=None), Column('cookie_id', Integer(), table=<cookies>, primary_key=True, nullable=False), Column('cookie_name', String(length=50), table=<cookies>), Column('cookie_recipe_url', String(length=255), table=<cookies>), Column('cookie_sku', String(length=55), table=<cookies>), Column('quantity', Integer(), table=<cookies>), Column('unit_cost', Numeric(precision=12, scale=2), table=<cookies>), schema=None)),\n",
       " PrimaryKeyConstraint(Column('cookie_id', Integer(), table=<cookies>, primary_key=True, nullable=False))}"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cookies.constraints"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-12-08T09:10:35.753427Z",
     "start_time": "2019-12-08T09:10:35.675741Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.sql.base.ImmutableColumnCollection at 0x10e71c2c8>"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cookies.c"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-12-08T09:13:14.364940Z",
     "start_time": "2019-12-08T09:13:14.343990Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{ForeignKeyConstraint(<sqlalchemy.sql.base.ColumnCollection object at 0x10e660d08>, None, table=Table('orders', MetaData(bind=None), Column('order_id', Integer(), table=<orders>, primary_key=True, nullable=False), Column('user_id', Integer(), ForeignKey('users.user_id'), table=<orders>), schema=None))}"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.foreign_key_constraints"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-12-08T09:22:57.501384Z",
     "start_time": "2019-12-08T09:22:57.478752Z"
    }
   },
   "outputs": [],
   "source": [
    "ForeignKeyConstraint?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  },
  "toc": {
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": "block",
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
